<?php
function rank_isExistID($id){
	$sql = "select * from rangking
			where RANK_ID = '$id'";
	
	$result = mysql_query($sql);
	$result = mysql_fetch_assoc($result);
	if ($res['LAPOR_ID'] === $id) return true;
	return false;
}

function rank_select($key=false, $where=false, $orderby=false, $groupby=false){
	$sql = "SELECT a.RANK_ID, a.KODE_KARYAWAN, b.NAMA_KARYAWAN, a.RANK_RANGKING,
					a.RANK_PERIODE, a.RANK_POINT 
			FROM rangking AS a, data_karyawan AS b ";
	$sql .= " WHERE a.KODE_KARYAWAN=b.KODE_KARYAWAN ";
	if ($key){
		$sql .= " AND ( a.RANK_ID	LIKE '%$key%' OR
					a.KODE_KARYAWAN LIKE '%$key%' OR
					a.RANK_RANGKING LIKE '%$key%' OR
					a.RANK_PERIODE LIKE '%$key%' OR
					a.RANK_POINT  LIKE '%$key%'
				  ) ";
	}
	if ($where){
		$sql .= " AND $where ";
	}
	if ($groupby){
		$sql .= " GROUP BY $groupby ";	
	}
	if ($orderby){
		$sql .= " ORDER BY $orderby";	
	}

	return mysql_query($sql);
}

function rank_load($id){ 
	$sql = "select * from rangking where RANK_ID='$id'";
	return mysql_query($sql);
}

function rank_exist($karyawanPelaku, $month, $year){
	$sql = "SELECT * from rangking 
			WHERE KODE_KARYAWAN='$karyawanPelaku' AND 
				  YEAR(RANK_PERIODE)='$year' AND 
				  MONTH(RANK_PERIODE)='$month' ";
	$res = mysql_query($sql);
	while ($row = mysql_fetch_assoc($res)) return $row['RANK_ID'];
	return false;
}

function rank_insert($RANK_ID, $KODE_KARYAWAN, $RANK_RANGKING, $RANK_PERIODE, $RANK_POINT){
	$sql = "INSERT INTO rangking ". 
			"values ('$RANK_ID', '$KODE_KARYAWAN', '$RANK_RANGKING', '$RANK_PERIODE', '$RANK_POINT')";
	$result = mysql_query($sql);
	return $result;
}

function rank_update($RANK_ID, $KODE_KARYAWAN=false, $RANK_RANGKING=false, $RANK_PERIODE=false, $RANK_POINT=false){
	$sqlSet = "";
	if ($KODE_KARYAWAN) $sqlSet  .= "KODE_KARYAWAN='$KODE_KARYAWAN'";
	if ($RANK_RANGKING) $sqlSet  .= ($sqlSet? "," :"") ."RANK_RANGKING='$RANK_RANGKING'";
	if ($RANK_PERIODE) $sqlSet  .= ($sqlSet? "," :"") ."RANK_PERIODE='$RANK_PERIODE'";
	if ($RANK_POINT) $sqlSet  .= ($sqlSet? "," :"") ."RANK_POINT='$RANK_POINT'";
	
	$sqlw = "RANK_ID='$RANK_ID'";
	$result = mysql_query("UPDATE rangking  SET $sqlSet WHERE $sqlw");
	return $result;
}

function rank_delete($id){
	$sql = "DELETE from rangking WHERE RANK_ID='$id'";
	$result = mysql_query($sql);
	return $result;
}


/**
 * $karyawanPelaku (String)
 * $periode (array) - month and year
 * @author freezy
 */
function rank_sum($karyawanPelaku, $month, $year){			
	/*
	 * 1. HItung POINT pada table PERBUATAN dengan periode $month, $year.
	 */ 
	$sql = "SELECT SUM( PERB_POINT ) as POINT 
			FROM PERBUATAN
			WHERE KODE_KARYAWAN =  '$karyawanPelaku'
				AND YEAR( PERB_TGL ) =  '$year'
				AND MONTH( PERB_TGL ) =  '$month'";
	$result = mysql_fetch_assoc( mysql_query($sql) );
	$currentPoint = $result['POINT'];
	
	/*
	 * 2. load nilai rangking periode sebelumnya
	 */
	//kurangi periode
	if ($month-1<=0) {
		$year--;
		$month=12;	
	}
	else{ $month--; }
	$sql = rank_select(false, "a.KODE_KARYAWAN='$karyawanPelaku' AND year(a.RANK_PERIODE)='$year' AND month(a.RANK_PERIODE)='$month'");
	$RANK = mysql_fetch_assoc($sql);
	$prevPoint = $RANK['RANK_POINT'];

	return $currentPoint + $prevPoint; 
}

/*
 * mengurutkan nomor rangking
 */
function rank_ranked($month, $year){
	$sql = "SELECT * FROM rangking 
			WHERE year(RANK_PERIODE)='$year' AND 
				  month(RANK_PERIODE)='$month' 
			ORDER BY RANK_POINT DESC";
	$result = mysql_query($sql);
	$CC=1;
	while ($row = mysql_fetch_assoc($result)){
		rank_update($row['RANK_ID'], false, $CC++, false, false);
	}
}